package com.onlineMIS.unit_testing;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Restrictions;
import org.hibernate.mapping.Array;
import org.springframework.aop.ThrowsAdvice;

import com.onlineMIS.ORM.entity.headQ.barcodeGentor.Category;
import com.onlineMIS.ORM.entity.headQ.barcodeGentor.Product;
import com.onlineMIS.unit_testing.entity.Category_tmp;

public class testCategoryTmp {
	public Session session;

	public testCategoryTmp() {
		Configuration configuration = new Configuration().configure();
		SessionFactory sFactory = configuration.buildSessionFactory();
		session = sFactory.openSession();
	}

	public void process() {
		Transaction transaction = session.beginTransaction();
		transaction.begin();
		
		String sqlString1 = "DELETE FROM Category_tmp";
		Query query = session.createSQLQuery(sqlString1);
		query.executeUpdate();

		
		Map<String, Integer> map = new HashMap<String, Integer>();
		map.put("女小童", 2194);
		map.put("女中童", 2195);
		map.put("女大童", 2196);
		map.put("男小童", 2197);
		map.put("男中童", 2198);
		map.put("男大童", 2199);
		
		Set<Integer> specialCategoryId = new HashSet<Integer>();
		specialCategoryId.add(1588);
		specialCategoryId.add(1905);
		specialCategoryId.add(7);
		specialCategoryId.add(854);
		specialCategoryId.add(732);
		specialCategoryId.add(2034);
		specialCategoryId.add(2105);

		String sqlString = "SELECT DISTINCT category.category_ID from Product where year_ID = 13 OR year_ID = 14  or year_id =12";

		Query q = session.createQuery(sqlString);
		List<Object> result = q.list();

		for (Object object : result) {
			int categoryId = (int) object;
			
			if (specialCategoryId.contains(categoryId)) {
				continue;
			}
			
			Category category = null;
			if (categoryId <= 2107) {
				category = selectCategoryById(categoryId);
			}

			if (category == null) {
				//insertLog(categoryId, 0, 0, 1);
				System.out.println("无法找到id : " + categoryId);
			} else {
				boolean matched = false;
				
				for (Map.Entry<String, Integer> categoryLevel : map.entrySet()) {
					try { 
					if (category.getCategory_Name().contains(categoryLevel.getKey())) {
						String categorySuffix = category.getCategory_Name().substring(
								category.getCategory_Name().indexOf("童") + 1, category.getCategory_Name().length());
						// category
						String categoryPrefix = category.getCategory_Name().substring(0,
								category.getCategory_Name().indexOf("童") + 1);

						Category upCategory = getCategory(categorySuffix);
						int upCategoryLevelId = map.get(categoryPrefix);
						
						int categoryNewId = 0;
						if (upCategory != null) {
							categoryNewId = upCategory.getCategory_ID();
							insertLog(categoryId, categoryNewId, upCategoryLevelId, category.getStatus());
						}
						matched = true;
						
						//if (category.getStatus() == Category.STATUS_GOOD)
					} 
					} catch (NullPointerException e) {
						System.out.println(category);
						e.printStackTrace();
						throw e;
					}
					}

				
				if (!matched) {
//					insertLog(categoryId, 0, 0, 0); // 在用货品类型，但货品类型不在map中,categoryLevel 为其他
					System.out.println(category);
				}
			}

		}
		
		insertDummy();

		transaction.commit();
		session.flush();
		session.close();
	}

	public static void main(String[] args) {

		testCategoryTmp testCategoryTmp = new testCategoryTmp();
		
		//生成categoryTmp 表
		//testCategoryTmp.process();
		
		testCategoryTmp.processProduct();

	}

	private void processProduct() {
		
		Transaction transaction = session.beginTransaction();
		transaction.begin();
		
		Session session2 = this.session;
		Criteria criteriaTwo = session2.createCriteria(Category_tmp.class);		
		List<Category_tmp> tmps = criteriaTwo.list();
		for (Category_tmp tmp : tmps) {
			String sqlString = "UPDATE Product SET category_level_1 = "+ tmp.getNewCategoryLevelId() + ",category_ID = " + tmp.getNewCategoryId() +"  WHERE	 category_ID="+ tmp.getOldCategoryId() ;
			System.out.println(sqlString);
			Query q = this.session.createSQLQuery(sqlString);
			int rows = q.executeUpdate();	
			System.out.println(tmp + " 更新 " + rows);
		}
		
		transaction.commit();
		session.flush();
		session.close();
		
	}

	/**
	 * 插入记录
	 * 
	 * @param categoryId
	 * @param categoryLevel1
	 * @param upCategoryId
	 * @param upCategoryLevel1
	 */
	public Category selectCategoryById(int categoryId) {

		Session session3 = this.session;

		Criteria allCriteria = session3.createCriteria(Category.class);

		allCriteria.add(Restrictions.eq("category_ID", categoryId));
//		allCriteria.add(Restrictions.eq("status", 1));
		
		if (categoryId == 2144)
			System.out.println(categoryId);
		List<Category> list = allCriteria.list();

		if (list.size() != 0) {
			return list.get(0);
		} else {
			return null;
		}

	}

	public Category getCategory(String categorySuffex) {

		Session session2 = this.session;
		Criteria criteriaTwo = session2.createCriteria(Category.class);
		criteriaTwo.add(Restrictions.eq("category_Name", categorySuffex));

		List<Object> objects = criteriaTwo.list();
		
		if (objects.size() == 0) {
//			System.out.println("货品类别为空-----货品名：" + categorySuffex);
			return null;
		}else {
			Category category = (Category) criteriaTwo.list().get(0);
			return category;
		}
		
	}

	/**
	 * 插入记录
	 * 
	 * @param categoryId
	 * @param categoryLevel1
	 * @param upCategoryId
	 * @param upCategoryLevel1
	 */
	public void insertLog(int categoryId, int upCategoryId, int upCategoryLevel1, int isExpire) {

		Session session3 = this.session;
		Criteria allCriteria = session3.createCriteria(Category_tmp.class);

		Category_tmp tmp = new Category_tmp();

		tmp.setOldCategoryId(categoryId);
		tmp.setNewCategoryId(upCategoryId);
		tmp.setNewCategoryLevelId(upCategoryLevel1);
		tmp.setIsExpire(isExpire);
		tmp.setCreateTime(new Date());

		session3.save(tmp);

	}

	public void insertDummy(){
		List<String> sqList = new ArrayList<String>();
		sqList.add("INSERT INTO Category_tmp (old_category_id,new_category_level_id,new_category_id,create_time,is_expire) values (1588, 2202, 1588,now(),1) ;" );
		sqList.add("INSERT INTO Category_tmp (old_category_id,new_category_level_id,new_category_id,create_time,is_expire) values (1905, 2202, 1905,now(),1);  " );
		sqList.add("INSERT INTO Category_tmp (old_category_id,new_category_level_id,new_category_id,create_time,is_expire) values (854, 2202, 854,now(),1); " );
		sqList.add("INSERT INTO Category_tmp (old_category_id,new_category_level_id,new_category_id,create_time,is_expire) values (7, 2202, 7,now(),1); " );
		sqList.add("INSERT INTO Category_tmp (old_category_id,new_category_level_id,new_category_id,create_time,is_expire) values (732, 2201, 732,now(),1); " );
		sqList.add("INSERT INTO Category_tmp (old_category_id,new_category_level_id,new_category_id,create_time,is_expire) values (2034, 2202, 2034,now(),1); " );
		sqList.add("INSERT INTO Category_tmp (old_category_id,new_category_level_id,new_category_id,create_time,is_expire) values (2105, 2202, 2105,now(),1); " );
		for (String sqlString : sqList) {
		Query q = this.session.createSQLQuery(sqlString);
		q.executeUpdate();
		}
	}
}
